﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using LeadVinaglue.Helpers;
using System.Data;
using LeadVinaglue.Models;

namespace LeadVinaglue.Repository
{
    public class SubCategoryRepository
    {
        LeadVinaglueEntities entities = new LeadVinaglueEntities();

        public IQueryable<SubCategory> GetAll(bool active)
        {
            return (from c in entities.SubCategories where (c.Active == active) orderby c.Name ascending select c);
        }

        public IQueryable<SubCategory> GetAllByCategory(int categoryId, bool active)
        {
            return (from c in entities.SubCategories
                    where (c.Active == active) && c.CategoryID == categoryId
                    orderby c.Name ascending
                    select c);
        }

        public List<OptionModel> GetAllItems()
        {
            List<OptionModel> list = new List<OptionModel>();

            var items = (from c in entities.SubCategories
                         orderby c.Name ascending
                         select c);

            list.Add(new OptionModel { ID = "-1", Name = "All" });
            // Add items
            foreach (var item in items)
            {
                OptionModel model = new OptionModel();
                model.ID = item.ID.ToString();
                model.ParentID = item.CategoryID.ToString();
                model.Name = item.Name;
                list.Add(model);
            }

            return list;
        }

        public List<Models.SubCategoryModel> GetAll()
        {
            List<Models.SubCategoryModel> list = new List<Models.SubCategoryModel>();

            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.Text, "SELECT * FROM [vwSubCategory]"))
            {
                while (item.Read())
                {
                    SubCategoryModel model = new SubCategoryModel();

                    model.ID = item.GetInt32(0);
                    model.Name = item.GetString(1);
                    if (!item.IsDBNull(2))
                        model.Ordered = item.GetInt32(2);
                    if (!item.IsDBNull(3))
                        model.DateCreated = Convert.ToDateTime(item.GetDateTime(3)).ToString("dd-MM-yyyy hh:mm tt");
                    model.Active = item.GetBoolean(4);
                    model.CategoryId = item.GetInt32(5);
                    model.CategoryName = item.GetString(6);

                    list.Add(model);
                }
            }

            return list;
        }

        public SubCategory GetSingle(int id)
        {
            return (from c in entities.SubCategories where c.ID == id select c).FirstOrDefault();
        }

        public void Create(SubCategory _object)
        {
            _object.DateCreated = System.DateTime.Now;
            entities.AddToSubCategories(_object);
            entities.SaveChanges();
        }

        public void Save(SubCategory _object)
        {
            entities.SaveChanges();
        }

        public void Delete(int id)
        {
            var _object = (from _list in entities.SubCategories where _list.ID == id select _list).First();
            entities.DeleteObject(_object);
            entities.SaveChanges();
        }
    }
}